clear
set more off
cd "C:\_Research\John\Replication data" // change directory before proceeding


* read tariff data
use "tariffs1990-2000.dta", clear
keep if year == 1990 | year == 1991 | year == 2000
drop mfn_ave
rename mexico_ave mex_t
reshape wide mex_t, i(hts8) j(year)
replace mex_t1990 = mex_t1991 if mex_t1990 ==. & mex_t1991 !=.
drop mex_t1991
reshape long mex_t, i(hts8) j(year)
drop if mex_t == .
save tariffs, replace

* converting trade and tariff data into IND1990
use "imports1990-2000.dta", clear
keep if year == 1990 
drop tot_im year
joinby hts8 using tariffs, unmatched(both)
drop if _m<3
drop _m
erase tariffs.dta

joinby hts8 using "concord_hts8_ind1990.dta", unmatched(master)
drop _m
sort year hts8 ind1990

* split imports between duplicate IND1990 codes matching to the same HTS8 code
replace mex_im = mex_im/2 if dup == 1
replace mex_im = mex_im/3 if dup == 2
replace mex_im = mex_im/4 if dup == 3

* create weighted Mexican tariffs before collapsing by IND1990
bys year ind1990: egen double wt = pc(mex_im), prop
replace wt = 0 if wt ==.
replace mex_t = mex_t*wt

collapse (sum) mex_t, by(ind1990 year)
reshape wide mex_t, i(ind1990) j(year)
gen mex_dt = (mex_t2000-mex_t1990)

rename mex_t1990 mex_t90
rename mex_t2000 mex_t00

* set agric t = 0
gen mex_t90_noag = mex_t90 
replace mex_t90_noag = 0 if ind1990 == 10 | ind1990 == 11
gen mex_dt_noag = mex_dt
replace mex_dt_noag = 0 if ind1990 == 10 | ind1990 == 11

label var mex_t90 "Tariff in 1990"
label var mex_t00 "Tariff in 2000"
label var mex_dt "Change in tariff"
label var mex_t90_noag "Tariff in 1990 (agric = 0)"
label var mex_dt_noag "Change in tariff (agric = 0)"

save "trade_ind1990_fixed", replace

* construct Mexico's RCA in 1990
insheet using "worldex19902000.csv", clear
drop tradeflowcode 
replace mexin1000usd = 0 if mexin1000usd == .
replace usain1000usd = 0 if usain1000usd == .
gen wldex = wldin1000usd - usain1000usd - mexin1000usd
drop mexin1000usd usain1000usd wldin1000usd
reshape wide wldex, i( productcode year) j( reporteriso3) str
replace wldexMEX = 0 if  wldexMEX ==.
gen worldex = wldexAll - wldexMEX
rename wldexMEX mexex
keep productcode year worldex mexex
rename productcode hts6
drop if hts6 == "9999AA"
destring hts6, replace
keep if year == 1990
save temp, replace

use "concord_hts8_ind1990.dta", clear
gen hts6 = floor(hts8/100)
drop hts8 dup
duplicates drop
duplicates tag hts6, gen(dup)

joinby hts6 using temp, unmatched(using)

* split imports between duplicate IND1990 codes matching to the same HTS8 code
replace mexex = mexex/2 if dup == 1
replace mexex = mexex/3 if dup == 2
replace mexex = mexex/4 if dup == 3
replace mexex = mexex/5 if dup == 4

replace worldex = worldex/2 if dup == 1
replace worldex = worldex/3 if dup == 2
replace worldex = worldex/4 if dup == 3
replace worldex = worldex/5 if dup == 4

collapse (sum) mexex worldex, by(ind1990)
egen mextot = total(mexex)
egen float wtot = total(worldex)
gen mex_rca = (mexex/worldex)/(mextot/wtot)
keep ind1990 mex_rca

save "mex_rca.dta", replace
erase temp.dta

use "main.dta", clear
keep if yr == 0
keep if empstat == 1

collapse (sum) emp = perwt, by(ind1990 conspuma)
sort ind1990 conspuma

joinby ind1990 using "trade_ind1990_fixed", unmatched(both)
drop _m
replace mex_t90 = 0 if mex_t90 == .
replace mex_t90_noag = 0 if mex_t90_noag == .
replace mex_dt = 0 if mex_dt == .
replace mex_dt_noag = 0 if mex_dt_noag == .

joinby ind1990 using "mex_rca.dta", unmatched(both)
replace mex_rca = 0 if mex_rca == .

gen emp_v1 = emp * mex_t90 * mex_rca
gen emp_v2 = emp * mex_dt * mex_rca
gen emp_v3 = emp * mex_t90_noag * mex_rca
gen emp_v4 = emp * mex_dt_noag * mex_rca
replace emp = emp * mex_rca

collapse (sum) emp emp_v*, by(conspuma)

gen locvul = emp_v1/emp
gen locdt = emp_v2/emp
gen locvul_noag = emp_v3/emp
gen locdt_noag = emp_v4/emp

keep conspuma locvul* locdt*

label var locvul "Average local tariff in 1990"
label var locdt "Change in average local tariff"
label var locvul_noag "Local tariff in 1990 (agric = 0)"
label var locdt_noag "Change in local tariff (agric = 0)"

save "locvul_mexrca_fixed.dta", replace


***********************************
* change in Chinese imports share *
***********************************

use "chn_imports1990-2000.dta", clear

joinby hts8 using "concord_hts8_ind1990.dta", unmatched(master)
drop _m
sort year hts8 ind1990

* split imports between duplicate IND1990 codes matching to the same HTS8 code
replace chn_im = chn_im/2 if dup == 1
replace chn_im = chn_im/3 if dup == 2
replace chn_im = chn_im/4 if dup == 3

replace tot_im = tot_im/2 if dup == 1
replace tot_im = tot_im/3 if dup == 2
replace tot_im = tot_im/4 if dup == 3

collapse (sum) tot_im chn_im, by(ind1990 year)
replace chn_im = 1 if ind1990 == 41 & year == 1990
gen chn_mshare =  chn_im/tot_im
drop tot_im

reshape wide chn_mshare chn_im, i(ind1990) j(year)
gen dchnmshare = chn_mshare2000-chn_mshare1990
drop if dchnmshare == 0
keep ind1990 dchnmshare 
label var dchnmshare "Change in imports from China as a share of total US imports"
save "chnmshare.dta", replace

use "main.dta", clear
keep if yr == 0
keep if empstat == 1

collapse (sum) emp = perwt, by(ind1990 conspuma)
sort ind1990 conspuma

joinby ind1990 using "chnmshare.dta", unmatched(both)

replace dchnmshare = 0 if dchnmshare == .

gen emp_v1 = emp * dchnmshare

collapse (sum) emp emp_v*, by(conspuma)

gen locdchnmshare = emp_v1/emp
keep conspuma loc*
label var locdchnmshare "Local employment weighted change in Chinese imports share"

joinby conspuma using "locvul_mexrca_fixed.dta"
save "locvul_mexrca_fixed.dta", replace


*****************************************
* alternative measure of vulnerability: *
* change in share of mexican imports    *
*****************************************

use "imports1990-2000.dta", clear
keep if year == 1990 | year == 2000
joinby hts8 using "concord_hts8_ind1990.dta", unmatched(master)
drop _m
sort year hts8 ind1990

//splitting imports between duplicate IND1990 codes matching to the same HTS8 code
replace mex_im = mex_im/2 if dup == 1
replace mex_im = mex_im/3 if dup == 2
replace mex_im = mex_im/4 if dup == 3
replace tot_im = tot_im/2 if dup == 1
replace tot_im = tot_im/3 if dup == 2
replace tot_im = tot_im/4 if dup == 3

collapse (sum) tot_im mex_im, by(ind1990 year)
gen mshare =  mex_im/ tot_im
drop mex_im tot_im
reshape wide mshare, i(ind1990) j(year)
gen dmshare = mshare2000-mshare1990
keep ind1990 dmshare
label var dmshare "Change in imports from Mexico as a share of total US imports"
save "dmshare.dta", replace

use "main.dta", clear
keep if yr == 0
keep if empstat == 1
collapse (sum) emp = perwt, by(ind1990 conspuma)
sort ind1990 conspuma
joinby ind1990 using "dmshare.dta", unmatched(both)
replace dmshare = 0 if dmshare == .
gen dmshare_noag = dmshare
replace dmshare_noag = 0 if ind1990 == 10 | ind1990 == 11
gen emp_v1 = emp * dmshare
gen emp_v2 = emp * dmshare_noag
collapse (sum) emp emp_v*, by(conspuma)
gen locdmshare = emp_v1/emp
gen locdmshare_noag = emp_v2/emp
keep conspuma locdmshare*
label var locdmshare "Local employment weighted change in Mexican imports share"
label var locdmshare_noag "Local employment weighted change in Mexican imports share (agric = 0)"
save "locdmshare.dta", replace
